﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class ChiTietPhieuThueDAO:AbstractDAO
    {
        public static bool them(ChiTietPhieuThueDTO chiTietPhieuThue)
        {
            bool ketQua = true;
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "INSERT INTO ChiTietPhieuThue(MaPhieuThue,MaKhachHang)";
                chuoiLenh += " VALUES (@MaPhieuThue,@MaKhachHang)";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@MaPhieuThue", OleDbType.Integer);
                thamSo.Value = chiTietPhieuThue.PhieuThue.MaPhieuThue;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@MaKhachHang", OleDbType.Integer);
                thamSo.Value = chiTietPhieuThue.KhachHang.MaKhachHang;
                lenh.Parameters.Add(thamSo);



                lenh.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ketQua = false;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return ketQua;
        }
        public static List<ChiTietPhieuThueDTO> layDSChiTietPhieuThue()
        {
            OleDbConnection ketNoi = null;
            List<ChiTietPhieuThueDTO> dsChiTietPhieuThue = new List<ChiTietPhieuThueDTO>();
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "SELECT LP.MaLoaiPhong,TenLoaiPhong,DonGia,P.MaPhong,TenPhong,GhiChu,SoKhachHienCo,PTP.MaPhieuThue,NgayBatDauThue,DaThanhToan,MaChiTietPhieuThue,KH.MaKhachHang,TenKhachHang,KH.MaLoaiKhach,CMND,DiaChi,TenLoaiKhach FROM Phong P,LoaiPhong LP,ChiTietPhieuThue CTPT,PhieuThuePhong PTP,KhachHang KH,LoaiKhach LK WHERE LP.MaLoaiPhong=P.MaLoaiPhong AND PTP.MaPhong=P.MaPhong AND CTPT.MaPhieuThue=PTP.MaPhieuThue AND CTPT.MaKhachHang=KH.MaKhachHang AND KH.MaLoaiKhach=LK.MaLoaiKhach";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbDataReader boDoc = lenh.ExecuteReader();
                while (boDoc.Read())
                {
                    ChiTietPhieuThueDTO chiTietPhieuThue = new ChiTietPhieuThueDTO();

                    LoaiPhongDTO loaiPhong = new LoaiPhongDTO();
                    loaiPhong.MaLoaiPhong = boDoc.GetInt32(0);
                    if (!boDoc.IsDBNull(1))
                        loaiPhong.TenLoaiPhong = boDoc.GetString(1);
                    loaiPhong.DonGia = boDoc.GetInt32(2);

                    PhongDTO phong = new PhongDTO();
                    phong.MaPhong = boDoc.GetInt32(3);
                    if (!boDoc.IsDBNull(4))
                        phong.TenPhong = boDoc.GetString(4);
                    if (!boDoc.IsDBNull(5))
                        phong.GhiChu = boDoc.GetString(5);
                    phong.LoaiPhong = loaiPhong;
                    phong.SoKhachHienCo = boDoc.GetInt32(6);

                    PhieuThuePhongDTO phieuThuePhong = new PhieuThuePhongDTO();
                    phieuThuePhong.MaPhieuThue = boDoc.GetInt32(7);
                    phieuThuePhong.NgayBatDauThue = boDoc.GetDateTime(8);
                    phieuThuePhong.Phong = phong;

                    if(!boDoc.IsDBNull(9))
                        phieuThuePhong.DaThanhToan = boDoc.GetInt32(9);

                    chiTietPhieuThue.MaChiTietPhieuThue = boDoc.GetInt32(10);

                    LoaiKhachDTO loaiKhach = new LoaiKhachDTO();
                    loaiKhach.MaLoaiKhach = boDoc.GetInt32(13);
                    if (!boDoc.IsDBNull(16))
                        loaiKhach.TenLoaiKhach = boDoc.GetString(16);

                    KhachHangDTO khachHang = new KhachHangDTO();
                    khachHang.MaKhachHang = boDoc.GetInt32(11);
                    if (!boDoc.IsDBNull(12))
                        khachHang.TenKhachHang = boDoc.GetString(12);
                    khachHang.LoaiKhach = loaiKhach;
                    if (!boDoc.IsDBNull(14))
                        khachHang.CMND = boDoc.GetString(14);
                    if (!boDoc.IsDBNull(15))
                        khachHang.DiaChi = boDoc.GetString(15);

                    
                    chiTietPhieuThue.KhachHang = khachHang;
                    chiTietPhieuThue.PhieuThue = phieuThuePhong;


                    dsChiTietPhieuThue.Add(chiTietPhieuThue);
                }

            }
            catch
            {
                dsChiTietPhieuThue = new List<ChiTietPhieuThueDTO>();
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsChiTietPhieuThue;
        }

    }
}
